import sqlite3 from "sqlite3";
import { open } from "sqlite";

const dbfile = "poem.db";

const conn = async () => {
  const db = await open({
    filename: dbfile,
    driver: sqlite3.Database,
  });
  return db;
};

export default {
  // 获取朝代列表
  async getDynasty() {
    let db = await conn();
    let result = await db.all(
      "SELECT name,  start_year,  end_year,  description FROM dynasty"
    );

    db.close();
    return result;
  },
  // 获取朝代列表
  async getDynastyDetail(name) {
    let db = await conn();
    let result = await db.get(
      "SELECT name,  start_year,  end_year,  description FROM dynasty where name=(?)",
      name
    );

    db.close();
    return result;
  },
  // 获取诗人列表
  async getPoets(
    name = null,
    dynasty = null,
    start_year = null,
    end_year = null
  ) {
    let db = await conn();
    let sql =
      "SELECT name, dynasty, start_year,  end_year,  description FROM poet ";
    let where = "where ";
    if (name != null) {
      where += `name like '%${name}%'`;
    }
    if (dynasty != null) {
      if (where.length > "where ".length) {
        where += " and ";
      }
      where += `dynasty = '${dynasty}'`;
    }
    if (start_year != null) {
      if (where.length > "where ".length) {
        where += " and ";
      }
      where += `start_year >= ${start_year}`;
    }
    if (end_year != null) {
      if (where.length > "where ".length) {
        where += " and ";
      }
      where += `end_year <= ${end_year}`;
    }
    if (where.length > "where ".length) {
      sql += where;
    }
    console.log(sql);
    let result = await db.all(sql);

    db.close();
    return result;
  },
};
